﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace PROSHOP.DAL
{
    public class SanPhamDAL
    {
        ProshopDataContext proshop;
        
        public SanPhamDAL()
        {
            proshop = new ProshopDataContext();
        }

        public DataSet dsSanPham(int chungloai, int loaihang, string namelike, int nsx, string sapxep, decimal minprice, decimal maxprice)
        {
            try
            {
                var s = from p in proshop.SanPhams
                        join q in proshop.LoaiHangs on new { MaLoaiSP = Convert.ToInt32(p.MaLoaiSP) } equals new { MaLoaiSP = q.MaLoaiHang }
                        join m in proshop.ChungLoais on new { MaChungLoai = Convert.ToInt32(q.MaChungLoai) } equals new { MaChungLoai = m.MaChungLoai }
                        select new
                        {
                            p.MaSP,
                            p.TenSP,
                            p.GiaBan,
                            p.NgayCapNhat,
                            p.MaLoaiSP,
                            p.MaNSX,
                            p.LaKhuyenMai,
                            p.HinhMH,
                            m.TenChungLoai,
                            mChungLoai = q.MaChungLoai
                        };

                if (chungloai != -1)
                {
                    s = s.Where(p=>p.mChungLoai == chungloai);
                }

                if (loaihang != -1)
                {
                    s = s.Where(p => p.MaLoaiSP == loaihang);
                }

                if (namelike != null)
                {
                    s = s.Where(p => p.TenSP.Contains(namelike));
                }

                if (nsx != -1)
                {
                    s = s.Where(p => p.MaNSX == nsx);
                }

                if (minprice != -1)
                {
                    s = s.Where(p => p.GiaBan > minprice);
                }

                if (maxprice != -1)
                {
                    s = s.Where(p => p.GiaBan < maxprice);
                }

                if (sapxep != "")
                {
                    s = s.OrderByDescending(p=>p.MaSP);
                }

                SqlCommand cmd = (SqlCommand)proshop.GetCommand(s);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception)
            {
                
                return null;
            }
        }

        public DataSet ctSanPham(int masp)
        {
            try
            {
                var s = from p in proshop.SanPhams
                        from q in proshop.NSXes
                        from m in proshop.LoaiHangs
                        from n in proshop.ChungLoais
                        where p.MaNSX == q.MaNSX && p.MaLoaiSP == m.MaLoaiHang && m.MaChungLoai == n.MaChungLoai && p.MaSP == masp
                        select new
                        {
                            p.MaSP,
                            p.TenSP,
                            p.GiaBan,
                            m.TenLoaiHang,
                            q.TenNsx,
                            p.NgayCapNhat,
                            p.HinhMH,
                            p.Status,
                            p.LaKhuyenMai,
                            p.VAT,
                            p.TongSoLuongNhap,
                            p.SoLuongBan,
                            p.NhaCungCap,
                            p.MoTa
                        };
                SqlCommand cmd = (SqlCommand)proshop.GetCommand(s);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception)
            {
                
                return null;
            }
        }
    }
}